﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient
Public Class MetodospagDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub


#Region "FuncionesUtilizables"

    Public Function existeMetodopag(ByVal pDescrMetodP As String) As Boolean
        Dim numReg As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM metodospago " & _
                                            "WHERE DesMetodoP = ?DesMetodoP;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?DesMetodoP", pDescrMetodP)
                    myConn.Open()
                    numReg = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (numReg > 0)
        Catch ex As Exception
            Throw
        Finally
            numReg = Nothing
        End Try
    End Function

    Public Function registraMetodospag(ByVal pMetodospag As MetodospagBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO metodospago(IdEmpresa,DesMetodoP, Status) " & _
                                            "VALUES (@IdEmpresa,@DesMetodoP, @Status);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@IdEmpresa", 0)
                    myComm.Parameters.AddWithValue("@DesMetodoP", pMetodospag.DesMetodoP)
                    myComm.Parameters.AddWithValue("@Status", "A")
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaMetodospag(ByVal pMetodospag As MetodospagBE, ByRef msg As String) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT count(*) " &
                                            "FROM metodospago " & _
                                            "WHERE IdMetodP <> @id " &
                                                "AND  DesMetodoP = @descripcion;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@id", pMetodospag.IdMetodP)
                    myComm.Parameters.AddWithValue("@descripcion", pMetodospag.DesMetodoP)
                    myConn.Open()
                    If (Convert.ToInt16(myComm.ExecuteScalar()) > 0) Then
                        msg &= "Existe otro registro con el Método de Pago especificado." & vbCrLf
                        Return False
                    End If

                    myComm.CommandText = "UPDATE metodospago " & _
                                            "SET DesMetodoP = @DesMetodoP, " & _
                                                "Status = @Status " & _
                                            "WHERE IdMetodP = @IdMetodP;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@DesMetodoP", pMetodospag.DesMetodoP)
                    Select Case pMetodospag.Status
                        Case MetodospagBE.EstatusMetodopag.Activo
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case MetodospagBE.EstatusMetodopag.Inactivo
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "A")
                    End Select
                    myComm.Parameters.AddWithValue("@IdMetodP", pMetodospag.IdMetodP)
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function cambiaStatus(ByVal pIdMetodoPago As Integer, ByVal pSetActivo As Boolean) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE metodospago " & _
                                            "SET Status = @Status " & _
                                            "WHERE IdMetodP = @IdMetodP;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    If (pSetActivo) Then
                        myComm.Parameters.AddWithValue("@Status", "A")
                    Else
                        myComm.Parameters.AddWithValue("@Status", "I")
                    End If
                    myComm.Parameters.AddWithValue("@IdMetodP", pIdMetodoPago)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getMetodopag(ByVal IdMetodP As Integer) As MetodospagBE
        Dim tmpmetodopag As MetodospagBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdEmpresa, IdMetodP, DesMetodoP, Status " & _
                                            "FROM metodospago " & _
                                            "WHERE IdMetodP = @IdMetodP;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@IdMetodP", IdMetodP)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpmetodopag = MetodospagFact.Build(myDR)
                        Else
                            tmpmetodopag = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpmetodopag

        Catch ex As Exception
            Throw
        Finally
            tmpmetodopag = Nothing
        End Try
    End Function

    Public Function getlistadoMetodospag(ByVal pDesMetodoP As String, ByVal tipoEstatus As Enumeraciones.estatusParaBusquedas) As List(Of MetodospagBE)
        Dim MetodospagLs As List(Of MetodospagBE) = New List(Of MetodospagBE)
        Dim qryEstatus As String = ""
        Select Case tipoEstatus
            Case Enumeraciones.estatusParaBusquedas.Todos
                'No se filtra
            Case Enumeraciones.estatusParaBusquedas.Activos
                qryEstatus = " AND Status = 'A' "
            Case Enumeraciones.estatusParaBusquedas.Inactivos
                qryEstatus = " AND Status = 'I' "
        End Select

        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdEmpresa, IdMetodP, DesMetodoP, Status " & _
                                           "FROM metodospago " & _
                                           "WHERE DesMetodoP LIKE @descMetodo " &
                                           qryEstatus & ";"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@descMetodo", "%" & pDesMetodoP & "%")
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim Metodospag As MetodospagBE = MetodospagFact.Build(myDR)
                            If (Not Metodospag Is Nothing) Then
                                MetodospagLs.Add(Metodospag)
                            End If
                            Metodospag = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return MetodospagLs
        Catch ex As Exception
            Throw
        Finally
            MetodospagLs = Nothing
            qryEstatus = Nothing
        End Try
    End Function

#End Region

End Class
